﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.IO;

using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;

using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.QuerySpec;
using SD.LLBLGen.Pro.QuerySpec.Adapter;

using VNet.VisiSuite.Dal.EntityClasses;
using VNet.VisiSuite.Dal.HelperClasses;
using VNet.VisiSuite.Dal.DatabaseSpecific;
using VNet.VisiSuite.Dal.FactoryClasses;

using VNet.VisiSuite.Common.Logger;


namespace VNet.VisiSuite.ProfitFabImport
{
    class ProfitFabData
    {
        public EntityCollection<StationEntity> Station { get; set; }
        public EntityCollection<SchdOperEntity> SchdOper { get; set; }
        public EntityCollection<SrouteEntity> SRoute { get; set; }
        public EntityCollection<SchdShipEntity> SchdShip { get; set; }
        public EntityCollection<OschdEntity> OSchd { get; set; }
        public EntityCollection<DrouteEntity> DRoute { get; set; }
        public EntityCollection<EmployeeEntity> Employee { get; set; }
        public EntityCollection<EmpTimeEntity> EmpTime { get; set; }
        public EntityCollection<EmpOperEntity> EmpOper { get; set; }
        public EntityCollection<FileTimeEntity> FileTime { get; set; }

        public FileTimeEntity StationFileTime { get; set; }
        public FileTimeEntity SchdOperFileTime { get; set; }
        public FileTimeEntity SRouteFileTime { get; set; }
        public FileTimeEntity DRouteFileTime { get; set; }
        public FileTimeEntity SchdShipFileTime { get; set; }
        public FileTimeEntity OSchdFileTime { get; set; }
        public FileTimeEntity EmployeeFileTime { get; set; }
        public FileTimeEntity EmpTimeFileTime { get; set; }
        public FileTimeEntity EmpOperFileTime { get; set; }
    }

    class Program
    {

        static private string _odbc_connection;
        static private string _src_path;
        static private string _dest_path;

        static void Main(string[] args)
        {

            FileLog.Error("ProfitFabImport started.");

            var data = getDataFromOdbc();

            if (data != null)
            {
                if (putDataToSql(data))
                {
                    ProfitFab.syncProfitFabOperations();
                    ProfitFab.syncProfitFabJobs();
                    ProfitFab.syncProfitFabEmployees();
                }
            }

        }

        static private bool updateDataFileIfNewer(FileTimeEntity fte)
        {
            bool updated = false;
            try
            {
                var now = DateTime.Now;
                var src = Path.Combine(_src_path, fte.FileName);
                var dest = Path.Combine(_dest_path, fte.FileName);
                var when = File.GetLastWriteTime(src);
                when = new DateTime(when.Year, when.Month, when.Day, when.Hour, when.Minute, when.Second); // truncte fractional seconds

                // FileLog.Error(fte.FileTime.ToString() + " - " + when.ToString() + " - " + (now - when).TotalSeconds.ToString());           

                if (when > fte.FileTime && (now - when).TotalSeconds > 20)
                {
                    File.Copy(src, dest, true);
                    fte.FileTime = when;
                    updated = true;
                }

            }
            catch (Exception ex)
            {
                FileLog.Error("updateDataFileIfNewer() - ", ex);
            }
            return updated;
        }

        static private EntityCollection<StationEntity> getStationData(FileTimeEntity fte)
        {
            EntityCollection<StationEntity> list = null; 

            try
            {
                if (updateDataFileIfNewer(fte))
                {
                    string qry = String.Format("SELECT NUM, DRONUM, ID DRONUM FROM STATION");
                    DataTable dt = executeOdbcQuery(qry);
                    StationEntity station;

                    if (dt != null)
                    {
                        list = new EntityCollection<StationEntity>();
                        foreach (DataRow row in dt.Rows)
                        {
                            station = new StationEntity()
                            {
                                StnNum = Convert.ToInt32(row[0]),
                                DroNum = Convert.ToInt32(row[1]),
                                Id = row[2].ToString()
                            };
                            list.Add(station);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                list = null;
                FileLog.Error("getStationData() - ", ex);
            }

            return list;
        }

        static private EntityCollection<SchdOperEntity> getSchdOperData(FileTimeEntity fte)
        {
            EntityCollection<SchdOperEntity> list = null;

            try
            {
                if (updateDataFileIfNewer(fte))
                {
                    string qry = String.Format("SELECT STNNUM, JOBNO, PASS, STARTDATE, STARTTIME FROM SCHDOPER");
                    DataTable dt = executeOdbcQuery(qry);
                    SchdOperEntity schd_oper;

                    if (dt != null)
                    {
                        list = new EntityCollection<SchdOperEntity>();
                        foreach (DataRow row in dt.Rows)
                        {
                            schd_oper = new SchdOperEntity()
                            {
                                StnNum = Convert.ToInt32(row[0]),
                                JobNo = row[1].ToString(),
                                Pass = Convert.ToByte(row[2]),
                                StartDate = (DateTime)row[3]
                            };
                            schd_oper.StartDate = schd_oper.StartDate.AddHours(Convert.ToDouble(row[4]));
                            list.Add(schd_oper);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                list = null;
                FileLog.Error("getSchdOperData() - ", ex);
            }

            return list;
        }

        static private EntityCollection<SrouteEntity> getSRouteData(FileTimeEntity fte)
        {
            EntityCollection<SrouteEntity> list = null;

            try
            {
                if (updateDataFileIfNewer(fte))
                {
                    string qry = String.Format("SELECT SETUPTIME, PARTTIME, PPTIME1, PPTIME2, STATUS, SCHNUM, DRONUM, PASS, RATE FROM SROUTE WHERE STATUS <> 'C'");
                    DataTable dt = executeOdbcQuery(qry);
                    SrouteEntity sroute;

                    if (dt != null)
                    {
                        list = new EntityCollection<SrouteEntity>();
                        foreach (DataRow row in dt.Rows)
                        {
                            sroute = new SrouteEntity()
                            {
                                SetupTime = Convert.ToDouble(row[0]),
                                PartTime = Convert.ToDouble(row[1]),
                                Pptime1 = Convert.ToDouble(row[2]),
                                Pptime2 = Convert.ToDouble(row[3]),
                                Status = row[4].ToString(),
                                SchNum = Convert.ToInt32(row[5]),
                                DroNum = Convert.ToInt32(row[6]),
                                Pass = Convert.ToByte(row[7]),
                                Rate = Convert.ToDecimal(row[8])
                            };
                            list.Add(sroute);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                list = null;
                FileLog.Error("getSRouteData() - ", ex);
            }

            return list;
        }

        static private EntityCollection<SchdShipEntity> getSchdShipData(FileTimeEntity fte)
        {
            EntityCollection<SchdShipEntity> list  = null;

            try
            {
                if (updateDataFileIfNewer(fte))
                {
                    string qry = String.Format("SELECT NUM, PARTNO, REV, DESC$, ACTPRODQTY, JOBNO, PARTNUM FROM SCHDSHIP");
                    DataTable dt = executeOdbcQuery(qry);
                    SchdShipEntity schd_ship;

                    if (dt != null)
                    {
                        list = new EntityCollection<SchdShipEntity>();
                        foreach (DataRow row in dt.Rows)
                        {
                            schd_ship = new SchdShipEntity()
                            {
                                SchNum = Convert.ToInt32(row[0]),
                                PartNo = row[1].ToString(),
                                Rev = row[2].ToString(),
                                Description = row[3].ToString(),
                                ActProdQty = (int)Convert.ToDouble(row[4]),
                                JobNo = row[5].ToString(),
                                PartNum = Convert.ToInt32(row[6])
                            };
                            list.Add(schd_ship);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                list = null;
                FileLog.Error("getSchdShipData() - ", ex);
            }

            return list;
        }

        static private EntityCollection<OschdEntity> getOSchdData(FileTimeEntity fte)
        {
            EntityCollection<OschdEntity> list = null;

            try
            {
                if (updateDataFileIfNewer(fte))
                {
                    string qry = String.Format("SELECT SCHNUM, DRONUM, PASS, STNNUM, SRTNUM FROM OSCHD");
                    DataTable dt = executeOdbcQuery(qry);
                    OschdEntity oschd;

                    if (dt != null)
                    {
                        list = new EntityCollection<OschdEntity>();
                        foreach (DataRow row in dt.Rows)
                        {
                            oschd = new OschdEntity()
                            {
                                SchNum = Convert.ToInt32(row[0]),
                                DroNum = Convert.ToInt32(row[1]),
                                Pass = Convert.ToByte(row[2]),
                                StnNum = Convert.ToInt32(row[3]),
                                SrtNum = Convert.ToInt32(row[4])
                            };
                            list.Add(oschd);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                list = null;
                FileLog.Error("getOSchdData() - ", ex);
            }

            return list;
        }

        static private EntityCollection<DrouteEntity> getDRouteData(FileTimeEntity fte)
        {
            EntityCollection<DrouteEntity> list = null;

            try
            {
                if (updateDataFileIfNewer(fte))
                {
                    string qry = String.Format("SELECT NUM, NAME FROM DROUTE");
                    DataTable dt = executeOdbcQuery(qry);
                    DrouteEntity droute;

                    if (dt != null)
                    {
                        list = new EntityCollection<DrouteEntity>();
                        foreach (DataRow row in dt.Rows)
                        {
                            droute = new DrouteEntity()
                            {
                                DroNum = Convert.ToInt32(row[0]),
                                Description = Convert.ToString(row[1]),
                            };
                            list.Add(droute);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                list = null;
                FileLog.Error("getDRouteData() - ", ex);
            }

            return list;
        }

        static private EntityCollection<EmployeeEntity> getEmployeeData(FileTimeEntity fte)
        {
            EntityCollection<EmployeeEntity> list = null;

            try
            {
                if (updateDataFileIfNewer(fte))
                {
                    string qry = String.Format("SELECT NUM, ID, NAME, SHIFT, TITLED FROM EMPLOYEE");
                    DataTable dt = executeOdbcQuery(qry);
                    EmployeeEntity emp;

                    if (dt != null)
                    {
                        list = new EntityCollection<EmployeeEntity>();
                        foreach (DataRow row in dt.Rows)
                        {
                            emp = new EmployeeEntity()
                            {
                                EmpNum = Convert.ToInt32(row[0]),
                                Id = row[1].ToString(),
                                Name = row[2].ToString(),
                                Shift = Convert.ToInt32(row[3]),
                                Title = row[4].ToString()
                            };
                            list.Add(emp);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                list = null;
                FileLog.Error("getEmployeeData() - ", ex);
            }

            return list;
        }

        private static DateTime? toDate(int clarion_date)
        {
            var date = new DateTime(1800, 12, 28, 0, 0, 0);
            return (clarion_date <= 0) ? (DateTime?)null : date.AddDays(clarion_date);
        }

        static private EntityCollection<EmpTimeEntity> getEmpTimeData(FileTimeEntity fte)
        {
            EntityCollection<EmpTimeEntity> list = null;

            try
            {
                if (updateDataFileIfNewer(fte))
                {

                    string qry = String.Format("SELECT EMPNUM, DRONUM, STNNUM, DATEIN, TIMEIN, DATEOUT, TIMEOUT, STATUS FROM EMPTIME");
                    DataTable dt = executeOdbcQuery(qry);
                    EmpTimeEntity emp_time;

                    if (dt != null)
                    {
                        list = new EntityCollection<EmpTimeEntity>();
                        foreach (DataRow row in dt.Rows)
                        {
                            emp_time = new EmpTimeEntity()
                            {
                                EmpNum = Convert.ToInt32(row[0]),
                                DroNum = Convert.ToInt32(row[1]),
                                StnNum = Convert.ToInt32(row[2]),
                                Status = row[7].ToString(),
                                DateIn = (DateTime)row[3],
                                DateOut = toDate(Convert.ToInt32(row[5]))
                            };
                            if (emp_time.DateIn.HasValue)
                                emp_time.DateIn = emp_time.DateIn.Value.AddHours(Convert.ToDouble(row[4]));
                            if (emp_time.DateOut.HasValue)
                                emp_time.DateOut = emp_time.DateOut.Value.AddHours(Convert.ToDouble(row[6])); 
                            list.Add(emp_time);
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                list = null;
                FileLog.Error("getEmpTimeData() - ", ex);
            }

            return list;
        }

        static private EntityCollection<EmpOperEntity> getEmpOperData(FileTimeEntity fte)
        {
            EntityCollection<EmpOperEntity> list = null;

            try
            {
                if (updateDataFileIfNewer(fte))
                {

                    string qry = String.Format("SELECT EMPNUM, DRONUM FROM EMPOPER");
                    DataTable dt = executeOdbcQuery(qry);
                    EmpOperEntity emp_oper;

                    if (dt != null)
                    {
                        list = new EntityCollection<EmpOperEntity>();
                        foreach (DataRow row in dt.Rows)
                        {
                            emp_oper = new EmpOperEntity()
                            {
                                EmpNum = Convert.ToInt32(row[0]),
                                DroNum = Convert.ToInt32(row[1])
                            };
                            list.Add(emp_oper);
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                list = null;
                FileLog.Error("getEmpOperData() - ", ex);
            }

            return list;
        }

        static private ProfitFabData getDataFromOdbc()
        {
            _odbc_connection = ConfigurationManager.AppSettings["ProfitFabConnection"];
            _src_path = ConfigurationManager.AppSettings["SrcPath"];
            _dest_path = ConfigurationManager.AppSettings["DestPath"];

            var data = new ProfitFabData();
            data.StationFileTime = new FileTimeEntity
            {
                FileName = "STATION.TPS",
                FileTime = DateTime.MinValue
            };
            data.SchdOperFileTime = new FileTimeEntity
            {
                FileName = "SCHDOPER.TPS",
                FileTime = DateTime.MinValue
            };
            data.SRouteFileTime = new FileTimeEntity
            {
                FileName = "SROUTE.TPS",
                FileTime = DateTime.MinValue
            };
            data.SchdShipFileTime = new FileTimeEntity
            {
                FileName = "SCHDSHIP.TPS",
                FileTime = DateTime.MinValue
            };
            data.OSchdFileTime = new FileTimeEntity
            {
                FileName = "OSCHD.TPS",
                FileTime = DateTime.MinValue
            };
            data.DRouteFileTime = new FileTimeEntity
            {
                FileName = "DROUTE.TPS",
                FileTime = DateTime.MinValue
            };
            data.EmployeeFileTime = new FileTimeEntity
            {
                FileName = "EMPLOYEE.TPS",
                FileTime = DateTime.MinValue
            };
            data.EmpTimeFileTime = new FileTimeEntity
            {
                FileName = "EMPTIME.TPS",
                FileTime = DateTime.MinValue
            };
            data.EmpOperFileTime = new FileTimeEntity
            {
                FileName = "EMPOPER.TPS",
                FileTime = DateTime.MinValue
            };
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    var qf = new QueryFactory();
                    var qry = qf.FileTime.OrderBy(FileTimeFields.FileTime.Descending());
                    var ftec = adapter.FetchQuery(qry);

                    foreach (FileTimeEntity fte in ftec)
                    {
                        switch (fte.FileName)
                        {
                            case "STATION.TPS":
                                data.StationFileTime = fte;
                                break;

                            case "SCHDOPER.TPS":
                                data.SchdOperFileTime = fte;
                                break;

                            case "SROUTE.TPS":
                                data.SRouteFileTime = fte;
                                break;

                            case "SCHDSHIP.TPS":
                                data.SchdShipFileTime = fte;
                                break;

                            case "OSCHD.TPS":
                                data.OSchdFileTime = fte;
                                break;

                            case "DROUTE.TPS":
                                data.DRouteFileTime = fte;
                                break;

                            case "EMPLOYEE.TPS":
                                data.EmployeeFileTime = fte;
                                break;

                            case "EMPTIME.TPS":
                                data.EmpTimeFileTime = fte;
                                break;

                            case "EMPOPER.TPS":
                                data.EmpOperFileTime = fte;
                                break;

                            default:
                                break;

                        }
                    }
                }
                catch (Exception ex)
                {
                    FileLog.Error("Error getting FileTime.", ex);
                }
            }
            
            data.Station = getStationData(data.StationFileTime);
            data.SchdOper = getSchdOperData(data.SchdOperFileTime);
            data.SRoute = getSRouteData(data.SRouteFileTime);
            data.SchdShip = getSchdShipData(data.SchdShipFileTime);
            data.OSchd = getOSchdData(data.OSchdFileTime);
            data.DRoute = getDRouteData(data.DRouteFileTime);
            data.Employee = getEmployeeData(data.EmployeeFileTime);
            data.EmpTime = getEmpTimeData(data.EmpTimeFileTime);
            data.EmpOper = getEmpOperData(data.EmpOperFileTime);

            return data;
        }

        static private void truncateSqlTable(DataAccessAdapter adapter, string table_name)
        {
            var qry = new ActionQuery(new SqlCommand("TRUNCATE TABLE ProfitFab.dbo." + table_name));
            int ret = adapter.ExecuteActionQuery(qry);
        }


        static private void FillError(object sender, FillErrorEventArgs args)
        {
          if (args.Errors.GetType() == typeof(System.OverflowException))
          {
            // Code to handle precision loss.
            //Add a row to table using the values from the first two columns.
            DataRow myRow = args.DataTable.Rows.Add(new object[]
               {args.Values[0], args.Values[1], DBNull.Value});
            //Set the RowError containing the value for the third column.
            //args.RowError = 
             //  "OverflowException Encountered. Value from data source: " +
             //  args.Values[2];
            args.Continue = true;
          }
        }

        static private DataTable executeOdbcQuery2(string query)
        {
            OdbcConnection myConnection = new OdbcConnection(_odbc_connection);
            OdbcCommand myCommand = new OdbcCommand("SELECT STATUS FROM EMPTIME", myConnection);
            myConnection.Open();
            OdbcDataReader myReader = myCommand.ExecuteReader();
            int cnt = 0;
            try
            {
                while (myReader.Read())
                {
                    ++cnt;
                    //Console.WriteLine(myReader.GetString(0));
                }
            }
            finally
            {
                myReader.Close();
                myConnection.Close();
            }
            return null;
        }

        static private DataTable executeOdbcQuery(string query)
        {
            DataTable dt = null;
            using (OdbcConnection connection = new OdbcConnection(_odbc_connection))
            {
                using (var da = new OdbcDataAdapter(query, connection))
                {
                    try
                    {
                        dt = new DataTable();
                        connection.Open();
                        da.FillError += new FillErrorEventHandler(FillError);
                        da.Fill(dt);
                    }
                    catch (System.Exception ex)
                    {
                        //Console.Write("Query failed: " + e.Message);
                        FileLog.Error("executeQuery() - ", ex);
                        dt = null;
                    }
                }
            }

            return dt;
        }

        static private DataSet executeOdbcQuery(string query, List<OdbcParameter> paras)
        {

            using (var da = new OdbcDataAdapter())
            {
                da.SelectCommand = new OdbcCommand();
                da.SelectCommand.Connection = new OdbcConnection(_odbc_connection);
                da.SelectCommand.CommandType = CommandType.Text; //
                da.SelectCommand.CommandText = query;

                if (paras != null)
                    foreach (var para in paras)
                        da.SelectCommand.Parameters.Add(para);

                DataSet data = new DataSet();
                try
                {
                    da.Fill(data);
                    //Console.WriteLine("Query succeeded");
                    return data;
                }
                catch (System.Exception ex)
                {
                    //Console.Write("Query failed: " + e.Message);
                    FileLog.Error("executeQuery() - ", ex);
                    return null;
                }
            }
        }

        static private bool putDataToSql(ProfitFabData data)
        {

            bool changed = false;
            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    adapter.StartTransaction(IsolationLevel.ReadCommitted, "syncProfitFabJobs");

                    if (data.Station != null)
                    {
                        truncateSqlTable(adapter, "Station");
                        //adapter.DeleteEntitiesDirectly("StationEntity", new RelationPredicateBucket((StationFields.PkId > 0)));
                        adapter.SaveEntityCollection(data.Station);
                        adapter.SaveEntity(data.StationFileTime);
                        changed = true;
                    }

                    if (data.SchdOper != null)
                    {
                        truncateSqlTable(adapter, "SchdOper");
                        adapter.SaveEntityCollection(data.SchdOper);
                        adapter.SaveEntity(data.SchdOperFileTime);
                        changed = true;
                    }

                    if (data.SchdShip != null)
                    {
                        truncateSqlTable(adapter, "SchdShip");
                        adapter.SaveEntityCollection(data.SchdShip);
                        adapter.SaveEntity(data.SchdShipFileTime);
                        changed = true;
                    }

                    if (data.SRoute != null)
                    {
                        truncateSqlTable(adapter, "SRoute");
                        adapter.SaveEntityCollection(data.SRoute);
                        adapter.SaveEntity(data.SRouteFileTime);
                        changed = true;
                    }

                    if (data.OSchd != null)
                    {
                        truncateSqlTable(adapter, "OSchd");
                        adapter.SaveEntityCollection(data.OSchd);
                        adapter.SaveEntity(data.OSchdFileTime);
                        changed = true;
                    }

                    if (data.DRoute != null)
                    {
                        truncateSqlTable(adapter, "DRoute");
                        adapter.SaveEntityCollection(data.DRoute);
                        adapter.SaveEntity(data.DRouteFileTime);
                        changed = true;
                    }

                    if (data.Employee != null)
                    {
                        truncateSqlTable(adapter, "Employee");
                        adapter.SaveEntityCollection(data.Employee);
                        adapter.SaveEntity(data.EmployeeFileTime);
                        changed = true;
                    }

                    if (data.EmpTime != null)
                    {
                        truncateSqlTable(adapter, "EmpTime");
                        adapter.SaveEntityCollection(data.EmpTime);
                        adapter.SaveEntity(data.EmpTimeFileTime);
                        changed = true;
                    }

                    if (data.EmpOper != null)
                    {
                        truncateSqlTable(adapter, "EmpOper");
                        adapter.SaveEntityCollection(data.EmpOper);
                        adapter.SaveEntity(data.EmpOperFileTime);
                        changed = true;
                    }

                    adapter.Commit();
                }
                catch (Exception ex)
                {
                    changed = false;
                    adapter.Rollback();
                    FileLog.Error("putDataToSql() - ", ex);
                }
            }

#if DEBUG
            changed = true;
#endif

            return changed;

        }

    }
}
